﻿using System;
using System.Collections;
using System.Data.SqlServerCe;
using System.Data;
using System.Configuration;
using System.Linq;

namespace Data_Access_Layer
{
    /// <summary>
    /// Klasa pozwalająca na uzyskanie połączenia z bazą (Singleton)
    /// </summary>
    public class DbManager
    {
        private static DbManager instance;
        private DBEntity db;

        private DbManager()
        {
            db = new DBEntity();
        }

        // Popisałem się wzorcem projektowym Singleton.
        // Zachęcam do lektury :D
        public static DbManager Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new DbManager();
                }
                return instance;
            }
        }

        #region Obłsuga bazy (repozytorium)
        #region Transakcje

        /// <summary>
        /// Metody zwraca wszystkie transakcje
        /// </summary>
        /// <param name="date">Data transakcji</param>
        public IQueryable<TransactionRow> GetTransactionsByDate(DateTime date)
        {
            IQueryable<TransactionRow> result = from t in db.Transactions
                                                join c in db.Categories
                                                on t.Category equals c.Id
                                                where t.Date == date
                                                select new TransactionRow { Id = t.Id, Description = t.Description, Value = t.Value, Category = c.Category };
            return result;
        }

        /// <summary>
        /// Usuwa transakcję po ID
        /// </summary>
        /// <param name="id">Parametr ID (GUID)</param>
        public void DeleteTransactionById(System.Guid id)
        {
            Transactions objToDelete = db.Transactions.Single(e => e.Id == id);
            try
            {
                db.DeleteObject(objToDelete);
                db.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }

        }

        /// <summary>
        /// Dodajemy transakcję (jako obiekt)
        /// </summary>
        /// <param name="transaction">Transactions object ORM</param>
        public void AddTrasaction(Transactions transaction)
        {
            try
            {
                db.AddToTransactions(transaction);
                db.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

        #region Kategorie

        /// <summary>
        /// Zwraca wszystkie kategorie
        /// </summary>
        public IQueryable<Categories> GetCategories()
        {
            return db.Categories.AsQueryable();
        }

        /// <summary>
        /// Dodawanie kategorii do bazy
        /// </summary>
        /// <param name="category">Kategoria w formie obiektowej</param>
        public void AddCategory(Categories category)
        {
            try
            {
                db.AddToCategories(category);
                db.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion
        #endregion

        /// <summary>
        /// Funkcja licząca bilans miesiąca
        /// </summary>
        /// <param name="date">Data końcowa</param>
        /// <returns>decimal bilans</returns>
        public decimal GetBalanceToDate(DateTime date)
        {
            int year = date.Year;
            int month = date.Month;
            int day = 1;
            DateTime monthStart = new DateTime(year, month, day);
            decimal balanceSum = (from t in db.Transactions
                                  where t.Date <= date
                                  where t.Date >= monthStart
                                  select (Decimal?)t.Value).Sum() ?? 0;
            return balanceSum;
        }

        /// <summary>
        /// Zwraca sumę w każdej kategorii danego miesiące
        /// </summary>
        /// <param name="date">Data, miesiące sam parsuje</param>
        public IEnumerable CategoriesSumByMonth(DateTime date, bool income)
        {
            int year = date.Year;
            int months = date.Month;
            int startDay = 1;
            DateTime monthStart = new DateTime(year, months, startDay);
            DateTime monthEnd = monthStart.AddMonths(1);
            return CategoriesSumByType(monthStart, monthEnd, income);
        }

        private IEnumerable CategoriesSumByType(DateTime monthStart, DateTime monthEnd, bool income)
        {
            IEnumerable categorySum = null;

            if (income)
            {
                var result = from t in db.Transactions
                             join c in db.Categories
                             on t.Category equals c.Id
                             where (t.Date <= monthEnd && t.Date >= monthStart) && t.Value >= 0
                             group t by c.Category into cat
                             select new { Category = cat.Key, Value = cat.Sum(i => i.Value) };
                categorySum = result;
            }
            else
            {
                var result = from t in db.Transactions
                             join c in db.Categories
                             on t.Category equals c.Id
                             where (t.Date <= monthEnd && t.Date >= monthStart) && t.Value < 0
                             group t by c.Category into cat
                             select new { Category = cat.Key, Value = cat.Sum(i => i.Value) };
                categorySum = result;
            }
            return categorySum;
        }
    }
}
